package org.jeecg.modules.oms.parser.impl.confirmation;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.modules.oms.entity.ProductSalesOrderDetail;
import org.jeecg.modules.oms.parser.WindowConfirmationFileParser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.beans.factory.annotation.Value;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 新格尔优化软件门确认单文件解析器
 * 专门用于解析新格尔优化软件导出的门确认单文件
 */
@Component
public class XinGeErDoorConfirmationFileParser implements WindowConfirmationFileParser {

    private static final Logger log = LoggerFactory.getLogger(XinGeErDoorConfirmationFileParser.class);

    // 从配置文件中注入上传路径
    @Value("${jeecg.path.upload}")
    private String uploadPath;

    /**
     * 解析新格尔优化软件的门确认单文件
     *
     * @param file     Excel文件
     * @param tenantId 租户ID
     * @return 产品销售订单明细列表
     * @throws IOException 文件读取异常
     * @throws Exception   其他解析异常
     */
    @Override
    public List<ProductSalesOrderDetail> parseFile(File file, Integer tenantId) throws IOException, Exception {
        List<ProductSalesOrderDetail> detailList = new ArrayList<>();
        
        try (InputStream is = new FileInputStream(file)) {
            Workbook workbook;
            
            // 根据文件后缀选择不同的Workbook实现
            if (file.getName().toLowerCase().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else if (file.getName().toLowerCase().endsWith(".xls")) {
                workbook = new HSSFWorkbook(is);
            } else {
                throw new IllegalArgumentException("不支持的文件格式，仅支持.xlsx和.xls格式");
            }
            
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            
            // 1. 解析订单基本信息
            Map<String, Object> orderInfo = extractOrderInfo(sheet);
            log.info("门订单基本信息: {}", orderInfo);

            // 2. 解析门产品明细信息
            List<ProductSalesOrderDetail> doorDetails = parseDoorDetails(sheet, tenantId, orderInfo);
            detailList.addAll(doorDetails);

            workbook.close();

            // 设置扩展信息
            if (!detailList.isEmpty()) {
                // 构建Controller需要的windowInfo信息
                Map<String, Object> windowInfo = new HashMap<>();
                
                // 下单日期：当前日期
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                windowInfo.put("orderDate", sdf.format(new Date()));
                
                // 交付日期：从J列获取
                String completionDate = findValueInColumn(sheet, 9); // J列索引为9
                if (completionDate != null) {
                    windowInfo.put("completionDate", completionDate);
                }
                
                // 订单编号：合同号M列
                String contractNo = findValueInColumn(sheet, 12); // M列索引为12
                if (contractNo != null) {
                    windowInfo.put("contractNo", contractNo);
                    windowInfo.put("orderNo", contractNo);
                }
                
                // 计算并添加金额汇总信息
                calculateAndSetAmountInfo(windowInfo, detailList);
                
                // 添加其他基本信息
                windowInfo.putAll(orderInfo);
                
                HashMap<String, Object> extendMap = new HashMap<>();
                extendMap.put("windowInfo", windowInfo);
                detailList.get(0).setExtendMap(extendMap);
            }
        }

        return detailList;
    }

    /**
     * 提取订单基本信息
     */
    private Map<String, Object> extractOrderInfo(Sheet sheet) {
        Map<String, Object> orderInfo = new HashMap<>();
        
        try {
            // 查找合同号（M列，第13列）
            String contractNo = findValueInColumn(sheet, 12); // M列索引为12
            if (contractNo != null) {
                orderInfo.put("orderNo", contractNo);
                orderInfo.put("contractNo", contractNo);
            }
            
            // 查找客户名称（B列，第2列）
            String customerName = findValueInColumn(sheet, 1); // B列索引为1
            if (customerName != null) {
                orderInfo.put("customerName", customerName);
            }
            
            // 查找客户地址（C列，第3列）
            String customerAddress = findValueInColumn(sheet, 2); // C列索引为2
            if (customerAddress != null) {
                orderInfo.put("customerAddress", customerAddress);
            }
            
            // 查找客户联系方式（E列，第5列）
            String customerContact = findValueInColumn(sheet, 4); // E列索引为4
            if (customerContact != null) {
                orderInfo.put("customerContact", customerContact);
            }
            
            // 查找交货日期（J列，第10列）
            String deliveryDate = findValueInColumn(sheet, 9); // J列索引为9
            if (deliveryDate != null) {
                orderInfo.put("deliveryDate", deliveryDate);
            }
            
            // 查找销售负责人（T列，第20列）
            String salesPerson = findValueInColumn(sheet, 19); // T列索引为19
            if (salesPerson != null) {
                orderInfo.put("salesPerson", salesPerson);
            }
            
            // 设置当前日期作为下单日期
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            orderInfo.put("orderDate", sdf.format(new Date()));
            
            log.info("提取到订单基本信息: 合同号={}, 客户={}, 地址={}, 联系方式={}, 交货日期={}, 销售负责人={}", 
                    contractNo, customerName, customerAddress, customerContact, deliveryDate, salesPerson);
            
        } catch (Exception e) {
            log.error("提取订单基本信息失败", e);
        }
        
        return orderInfo;
    }

    /**
     * 在指定列中查找有效值
     */
    private String findValueInColumn(Sheet sheet, int columnIndex) {
        for (int i = 0; i <= Math.min(sheet.getLastRowNum(), 50); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            Cell cell = row.getCell(columnIndex);
            if (cell != null) {
                String value = getCellValueAsString(cell).trim();
                if (!value.isEmpty() && !isHeaderOrLabel(value)) {
                    log.debug("在列{}找到值: {}", getColumnName(columnIndex), value);
                    return value;
                }
            }
        }
        
        return null;
    }

    /**
     * 判断是否是表头或标签
     */
    private boolean isHeaderOrLabel(String value) {
        // 过滤掉常见的表头和标签
        String[] headers = {"合同号", "客户名称", "客户地址", "联系方式", "交货日期", "销售负责人", 
                           "门窗编号", "门窗系列", "门窗颜色", "总面积", "门窗金额", "序号", "编号"};
        for (String header : headers) {
            if (value.contains(header)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 获取列名（A, B, C...）
     */
    private String getColumnName(int columnIndex) {
        return String.valueOf((char) ('A' + columnIndex));
    }

    /**
     * 解析门产品明细信息
     */
    private List<ProductSalesOrderDetail> parseDoorDetails(Sheet sheet, Integer tenantId, Map<String, Object> orderInfo) {
        List<ProductSalesOrderDetail> details = new ArrayList<>();
        
        try {
            // 查找数据开始行（通常在表头"序号"、"订单编号"等之后）
            int dataStartRow = 1;
            
            // 获取表头映射
            Map<String, Integer> headerMap = buildHeaderMap(sheet, dataStartRow - 1);
            log.info("表头映射: {}", headerMap);
            
            // 按合同号分组处理数据行
            Map<String, List<Row>> contractGroups = groupRowsByContract(sheet, dataStartRow, headerMap);
            
            // 处理每个合同组
            for (Map.Entry<String, List<Row>> entry : contractGroups.entrySet()) {
                String contractNo = entry.getKey();
                List<Row> contractRows = entry.getValue();
                
                log.info("处理合同号: {}, 明细数量: {}", contractNo, contractRows.size());
                
                // 处理该合同下的所有明细
                for (Row row : contractRows) {
                    ProductSalesOrderDetail detail = parseRowToDetail(row, headerMap, tenantId, orderInfo, contractNo);
                    if (detail != null) {
                        details.add(detail);
                    }
                }
            }
            
        } catch (Exception e) {
            log.error("解析门产品明细失败", e);
        }
        
        return details;
    }

    /**
     * 构建表头映射
     */
    private Map<String, Integer> buildHeaderMap(Sheet sheet, int headerRow) {
        Map<String, Integer> headerMap = new HashMap<>();
        
        Row row = sheet.getRow(headerRow);
        if (row == null) return headerMap;
        
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (cell != null) {
                String header = getCellValueAsString(cell).trim();
                if (!header.isEmpty()) {
                    headerMap.put(header, j);
                }
            }
        }
        
        return headerMap;
    }

    /**
     * 按合同号分组数据行
     */
    private Map<String, List<Row>> groupRowsByContract(Sheet sheet, int dataStartRow, Map<String, Integer> headerMap) {
        Map<String, List<Row>> contractGroups = new HashMap<>();
        
        // 合同号在M列（索引12）
        int contractColIndex = 12;
        
        for (int i = dataStartRow; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            // 检查是否是空行
            if (isEmptyRow(row)) continue;
            
            Cell contractCell = row.getCell(contractColIndex);
            if (contractCell != null) {
                String contractNo = getCellValueAsString(contractCell).trim();
                if (!contractNo.isEmpty() && !isHeaderOrLabel(contractNo)) {
                    contractGroups.computeIfAbsent(contractNo, k -> new ArrayList<>()).add(row);
                }
            }
        }
        
        return contractGroups;
    }

    /**
     * 解析行数据为订单明细
     */
    private ProductSalesOrderDetail parseRowToDetail(Row row, Map<String, Integer> headerMap, 
                                                   Integer tenantId, Map<String, Object> orderInfo, String contractNo) {
        try {
            ProductSalesOrderDetail detail = new ProductSalesOrderDetail();
            detail.setId(UUID.randomUUID().toString().replace("-", ""));
            detail.setTenantId(tenantId);
            
            // 设置订单ID为合同号
            detail.setOrderId(contractNo);
            
            // 直接按列索引解析字段
            // 门窗编号：A列（索引0）
            String windowNo = getCellValueAsString(row.getCell(0)).trim();
            if (!windowNo.isEmpty()) {
                detail.setWindowNo(windowNo);
            }
            
            // 门窗系列：D列（索引3）
            String windowSeries = getCellValueAsString(row.getCell(3)).trim();
            if (!windowSeries.isEmpty()) {
                detail.setWindowSeries(windowSeries);
            }
            
            // 门窗颜色：I列（索引8）
            String windowColor = getCellValueAsString(row.getCell(8)).trim();
            if (!windowColor.isEmpty()) {
                detail.setWindowColor(windowColor);
            }

            //门窗扇数： X列
            Integer quantity = Integer.valueOf(getCellValueAsString(row.getCell(23)).trim());
            if(quantity != null){
                detail.setQuantity(quantity);
            }

            // 门窗金额：F列（索引5）
            String amountStr = getCellValueAsString(row.getCell(5)).trim();
            if (!amountStr.isEmpty()) {
                try {
                    detail.setAmount(new BigDecimal(amountStr.replaceAll("[^0-9.]", "")));
                } catch (NumberFormatException e) {
                    detail.setAmount(BigDecimal.ZERO);
                }
            }

            //单价  金额amountStr / 扇数quantity
            if(amountStr != null && quantity != null){
                BigDecimal unitPrice =  detail.getAmount().divide(new BigDecimal(quantity), 2, RoundingMode.HALF_UP);
                detail.setUnitPrice(unitPrice);
            }

            // 总面积：H列（索引7）
            String totalAreaStr = getCellValueAsString(row.getCell(7)).trim();
            if (!totalAreaStr.isEmpty()) {
                try {
                    detail.setTotalArea(new BigDecimal(totalAreaStr.replaceAll("[^0-9.]", "")));
                } catch (NumberFormatException e) {
                    detail.setTotalArea(BigDecimal.ZERO);
                }
            }

            //技术要求：G列（索引6）
            String technicalRequirements = getCellValueAsString(row.getCell(6)).trim();
            if(!technicalRequirements.isEmpty()){
                detail.setTechnicalRequirements(technicalRequirements);
            }

            //备注：U列（索引20）
            String remark = getCellValueAsString(row.getCell(20)).trim();
            if(!remark.isEmpty()){
                detail.setRemark(remark);
            }

            // 解析扩展字段到extendMap（实收金额和未收款金额）
            parseFinancialFields(detail, row);
            
            return detail;
            
        } catch (Exception e) {
            log.error("解析行数据失败", e);
            return null;
        }
    }


    /**
     * 解析财务相关字段
     */
    private void parseFinancialFields(ProductSalesOrderDetail detail, Row row) {
        Map<String, Object> extendMap = detail.getExtendMap();
        if (extendMap == null) {
            extendMap = new HashMap<>();
            detail.setExtendMap(extendMap);
        }
        
        // 实收金额：V列（索引21）
        String receivedAmountStr = getCellValueAsString(row.getCell(21)).trim();
        if (!receivedAmountStr.isEmpty()) {
            try {
                BigDecimal receivedAmount = new BigDecimal(receivedAmountStr.replaceAll("[^0-9.]", ""));
                extendMap.put("receivedAmount", receivedAmount);
            } catch (NumberFormatException e) {
                extendMap.put("receivedAmount", BigDecimal.ZERO);
            }
        } else {
            extendMap.put("receivedAmount", BigDecimal.ZERO);
        }
        
        // 未收金额：W列（索引22）
        String unpaidAmountStr = getCellValueAsString(row.getCell(22)).trim();
        if (!unpaidAmountStr.isEmpty()) {
            try {
                BigDecimal unpaidAmount = new BigDecimal(unpaidAmountStr.replaceAll("[^0-9.]", ""));
                extendMap.put("unpaidAmount", unpaidAmount);
            } catch (NumberFormatException e) {
                extendMap.put("unpaidAmount", BigDecimal.ZERO);
            }
        } else {
            extendMap.put("unpaidAmount", BigDecimal.ZERO);
        }
    }

    /**
     * 计算并设置金额汇总信息
     */
    private void calculateAndSetAmountInfo(Map<String, Object> windowInfo, List<ProductSalesOrderDetail> detailList) {
        BigDecimal totalAmount = BigDecimal.ZERO;        // 销售总价（F列汇总）
        BigDecimal totalReceivedAmount = BigDecimal.ZERO; // 实收金额总计（V列汇总）
        BigDecimal totalUnpaidAmount = BigDecimal.ZERO;   // 未收款总计（W列汇总）
        
        // 遍历所有订单明细，从原始Excel数据中重新计算金额
        try {
            // 由于我们需要访问原始Excel数据，这里需要从sheet重新读取
            // 为了简化，我们从已解析的detailList中获取金额信息
            for (ProductSalesOrderDetail detail : detailList) {
                // 销售总价
                if (detail.getAmount() != null) {
                    totalAmount = totalAmount.add(detail.getAmount());
                }
                
                // 从扩展字段中获取实收金额和未收金额
                Map<String, Object> extendMap = detail.getExtendMap();
                if (extendMap != null) {
                    if (extendMap.containsKey("receivedAmount")) {
                        BigDecimal receivedAmount = (BigDecimal) extendMap.get("receivedAmount");
                        if (receivedAmount != null) {
                            totalReceivedAmount = totalReceivedAmount.add(receivedAmount);
                        }
                    }
                    
                    if (extendMap.containsKey("unpaidAmount")) {
                        BigDecimal unpaidAmount = (BigDecimal) extendMap.get("unpaidAmount");
                        if (unpaidAmount != null) {
                            totalUnpaidAmount = totalUnpaidAmount.add(unpaidAmount);
                        }
                    }
                }
            }
            
            // 设置到windowInfo中供Controller使用
            windowInfo.put("totalAmount", totalAmount);           // 销售总价
            windowInfo.put("receivedAmount", totalReceivedAmount); // 实收金额
            windowInfo.put("unpaidAmount", totalUnpaidAmount);     // 未收款金额
            
            log.info("门订单金额汇总 - 销售总价: {}, 实收金额: {}, 未收款: {}", 
                    totalAmount, totalReceivedAmount, totalUnpaidAmount);
                    
        } catch (Exception e) {
            log.error("计算金额汇总信息失败", e);
            // 设置默认值
            windowInfo.put("totalAmount", BigDecimal.ZERO);
            windowInfo.put("receivedAmount", BigDecimal.ZERO);
            windowInfo.put("unpaidAmount", BigDecimal.ZERO);
        }
    }

    /**
     * 检查是否是空行
     */
    private boolean isEmptyRow(Row row) {
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (cell != null) {
                String value = getCellValueAsString(cell).trim();
                if (!value.isEmpty()) {
                    return false;
                }
            }
        }
        return true;
    }

    /**
     * 获取单元格值为字符串
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(cell.getDateCellValue());
                } else {
                    // 处理数字，避免科学计数法
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == (long) numericValue) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    return cell.getStringCellValue();
                }
            default:
                return "";
        }
    }
}
